# 五、点餐管理系统
#     1、餐馆可以新增菜品、下架菜品、修改菜品，点菜
#     2、每个菜品，都有对应的库存，所有菜品的库存均为999，每出一道菜，库存-1
#     菜品有对应的价格，餐厅老板定义价格每道菜不允许超过15元，没有小数单位，均为整数
#     3、餐馆后台有点赞系统，能够收集客户对菜品的喜好，0~5分
#     4、有菜品评分提报功能，对平均分数（菜品总分/打分人数）低于3分的菜品进行强制下架

import pymysql


class OrderDishMangeSystem:
    def __init__(self):
        self.db = pymysql.connect(
            host='',
            port=3306,
            user='root',
            password='',
            database='ODMS'
        )
        self.cursor = self.db.cursor()

    def add_dish(self, name, price):
        try:
            select_dishes = 'select dish_name,enable from t_dishes where dish_name = %s and enable=0;'
            self.cursor.execute(select_dishes, name)
            select_result = self.cursor.fetchall()
            if select_result:
                update_dish_sql = 'update t_dishes set enable=1 , price=%s , inventory=999 where dish_name =%s;'
                self.cursor.execute(update_dish_sql, (price, name))
                self.db.commit()
                return f'{name}上架成功！'
            if 1 <= price <= 15:
                add_dish_sql = 'insert into t_dishes (dish_name,price) values (%s,%s);'
                self.cursor.execute(add_dish_sql, (name, price))
                self.db.commit()
                return f'新增{name}成功！'
            else:
                return f'你的价格输入有误，每个菜品不允许超过15元，定价只能是整数'
        except Exception as e:
            return f'您的输入有误，错误原因是：{e}'

    def delete_dish(self, dish_name):
        try:
            delete_sql = 'update t_dishes set enable = 0 where dish_name = %s;'
            self.cursor.execute(delete_sql, dish_name)
            self.db.commit()
            return f'{dish_name}下架成功！'
        except Exception as e:
            return f'您的输入有误，错误原因是：{e}'

    def update_dish(self, dish_name, newprice):
        try:
            if 1 <= newprice <= 15:
                update_sql = 'update t_dishes set price = %s where dish_name = %s;'
                self.cursor.execute(update_sql, (newprice, dish_name))
                self.db.commit()
                return f'{dish_name}价格修改成功！'
            else:
                return f'你的价格输入有误，每个菜品不允许超过15元，定价只能是整数'
        except Exception as e:
            return f'您的输入有误，错误原因是：{e}'

    def order_dish(self, dish_name, amount, who):
        # 点餐成功的条件 1、这个菜存在并且库存足够 2、这个菜是上架状态的
        # 点餐失败的条件 1、菜不存在 2、库存不足 3、菜是下架状态
        try:
            selet_dish = 'select dish_name,inventory,enable from t_dishes where dish_name = %s;'
            self.cursor.execute(selet_dish, dish_name)
            soure_result = self.cursor.fetchall()
            result = []
            for line in soure_result:
                line_dict = {}  # 每行的内容保存到该字典中
                for i, j in enumerate(self.cursor.description):
                    line_dict[j[0]] = line[i]
                result.append(line_dict)
            if not result:
                return f'{dish_name}不在菜单中，无法下单！'
            elif result[0].get('enable') == 0:
                fail_reason_sql = 'insert into t_record (dish_name, num, who, is_success,fail_reason) values (%s,%s,%s,0,%s);'
                self.cursor.execute(fail_reason_sql, (dish_name, amount, who, '商品已经下架，无法下单'))
                self.db.commit()
                return f'{dish_name}已经下架，无法下单！'
            elif result[0].get('inventory') < amount:
                fail_reason_sql = 'insert into t_record (dish_name, num, who, is_success,fail_reason) values (%s,%s,%s,0,%s);'
                self.cursor.execute(fail_reason_sql, (dish_name, amount, who, '商品库存不足，无法下单'))
                self.db.commit()
                return f'{dish_name}库存不足，无法下单！'
            else:
                order_sql = 'update t_dishes set inventory = %s where dish_name = %s;'
                add_record = 'insert into t_record (dish_name, num, who, is_success) values (%s,%s,%s,1);'
                self.cursor.execute(order_sql, (result[0]['inventory'] - amount, dish_name))
                self.cursor.execute(add_record, (dish_name, amount, who))
                self.db.commit()
                return f'{dish_name}点餐成功！'

        except Exception as e:
            return f'您的输入有误，错误原因是：{e}'

    def dish_comment(self, dish_name, score, who, comment=None):
        # 可以评价的条件：点过这个菜且成功了！
        # 不能条件的条件：1、这个人没有这个菜品点菜记录，2、点过但是失败了! 3、这个菜不存在
        try:
            select_who_record = 'select dish_name,is_success from t_record where who =%s and dish_name=%s;'
            self.cursor.execute(select_who_record, (who, dish_name))
            soure_result = self.cursor.fetchall()
            result = []
            for line in soure_result:
                line_dict = {}  # 每行的内容保存到该字典中
                for i, j in enumerate(self.cursor.description):
                    line_dict[j[0]] = line[i]
                result.append(line_dict)
            ordered = False  # 点菜成功标识
            for d in result:
                if d['is_success'] == 1:
                    ordered = True
                    break
            if not result or not ordered:
                return f'{who}没有点过{dish_name},无法对它评价！'
            else:
                comment_sql = 'insert into t_dish_comment values (%s,%s,%s,%s);'
                self.cursor.execute(comment_sql, (dish_name, max(0, min(score, 5)), who, comment))
                self.db.commit()
                return '评价成功！'
        except Exception as e:
            return f'您的输入有误，错误原因是：{e}'

    def inspection(self):
        # 有菜品评分提报功能，对平均分数（菜品总分/打分人数）低于3分的菜品进行强制下架
        # 实现逻辑：先对t_dish_comment 查询每个菜的平均分，获取低于3分的菜品名称，去t_dishes表中下架该菜品
        try:
            dish_avg_score = 'select dish_name from t_dish_comment group by dish_name HAVING avg(score) <3'
            self.cursor.execute(dish_avg_score)
            source_result = self.cursor.fetchall()
            result = tuple(i[0] for i in source_result)
            if not result:
                return None
            else:
                for i in result:
                    self.delete_dish(i)
                return f'{result} 下架成功！'
        except Exception as e:
            self.db.rollback()
            return f'您的输入有误，错误原因是：{e}'


odms = OrderDishMangeSystem()
print(odms.add_dish('炒白菜', 3))